package com.itany.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author MissChen
 * @version 1.0
 * @date 2023 年12月13日 13:55
 */
public class Test11_批处理 {
    public static void main(String[] args) {
        try {
            insertUser();
            insertUser2();
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }
    public static void insertUser() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn=JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_user ")
                    .append(" (username,password,phone,address) ")
                    .append(" values ")
                    .append(" (?,?,?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql);
            long begin=System.currentTimeMillis();
            for(int i=1;i<=1000;i++){
                ps.setString(1,"alice");
                ps.setString(2,"123456");
                ps.setString(3,"15896587845");
                ps.setString(4,"重庆");
                ps.executeUpdate();
            }
            conn.commit();
            long end=System.currentTimeMillis();
            System.out.println("添加1000条数据公耗费了"+(end-begin)+"毫秒");


        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("访问数据失败");
        } finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

    public static void insertUser2() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn=JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_user ")
                    .append(" (username,password,phone,address) ")
                    .append(" values ")
                    .append(" (?,?,?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql);
            long begin=System.currentTimeMillis();
            for(int i=1;i<=1000;i++){
                ps.setString(1,"alice");
                ps.setString(2,"123456");
                ps.setString(3,"15896587845");
                ps.setString(4,"重庆");

                //将ps对象添加到缓存区，将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }

            //批量执行
            ps.executeBatch();
            conn.commit();
            long end=System.currentTimeMillis();
            System.out.println("添加1000条数据公耗费了"+(end-begin)+"毫秒");


        } catch (DataAccessException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                throw new DataAccessException("回滚事务失败");
            }
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
               throw new DataAccessException("回滚事务失败");
            }
            e.printStackTrace();
            throw new DataAccessException("访问数据失败");
        } finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

}
